/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 4
 */
public class DBSalesOrder implements IFDBSalesOrder {

    private  Connection con;
    
    public DBSalesOrder() {
        con = DbConnection1.getInstance().getDBcon();
    }
    
    // get all SalesOrder
    @Override
    public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    //get one sales order having the specified order id
    @Override
    public SalesOrder findSalesOrder(int orderId, boolean retriveAssociation)
    {
        String wClause = "  orderId = '" + orderId + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    
    //insert a new sales order
    @Override
    public int insertSalesOrder(SalesOrder salesOrder)
    {
        //call to get the next order id
        int nextOrderId = GetMax.getMaxId("Select max(orderId) from SalesOrder");
        nextOrderId = nextOrderId + 1;
        System.out.println("next order id = " +  nextOrderId);
  
       int rc = -1;
	   String query="INSERT INTO SalesOrder(orderId, date, deliveryStatus, deliveryDate, total, customerId)  VALUES('"+
                        nextOrderId + "','" +
                        salesOrder.getDate()  + "','"  +
		    	salesOrder.getDeliveryStatus()  + "','"  +
                        salesOrder.getDeliveryDate() + "','" +
                        salesOrder.getTotal() + "','" +
                        salesOrder.getCustomer().getCustomerId() + "')";
                        //salesOrder.getInvoice().getInvoiceNo() + "')";              
          
      System.out.println("insert : " + query);		  					
      try{ // insert new sales order
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in sales order db: "+ex);
	   }
	   return(rc);
    
    }
    
    //update information about a SalesOrder
    @Override
    public int updateSalesOrder(SalesOrder salesOrder)
    {
        SalesOrder saleObj  = salesOrder;
		int rc=-1;

		String query="UPDATE SalesOrder SET "+
		 	  "deliveryStatus ='"+ saleObj.getDeliveryStatus()+"', " +
                        "invoiceNo ='"+ saleObj.getInvoice().getInvoiceNo()+"' " +
                        " WHERE OrderId = '"+ saleObj.getOrderId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update SalesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in SalesOrder db: "+ex);
	  	}
		return(rc);
    }
    
    //update information about a SalesOrder
    public int updateSalesOrderTotal(SalesOrder salesOrder)
    {
        SalesOrder saleObj  = salesOrder;
		int rc=-1;

		String query="UPDATE SalesOrder SET "+
		 	  "total ='"+ saleObj.getTotal()+"' " +
                        " WHERE OrderId = '"+ saleObj.getOrderId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update SalesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in SalesOrder db: "+ex);
	  	}
		return(rc);
    }
    
    public int delete(int orderId)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM SalesOrder WHERE orderId = '" +
				orderId + "'";
                System.out.println(query);
	  	try{ // delete from SalesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in SalesOrder db: "+ex);
   	        }
		return(rc);
	}
    
    @Override
        public void calcTotalAmounts(SalesOrder salesOrder)
        {
            double total = 0;
            
            IFDBSalesLineItem dbSli = new DBSalesLineItem();
            ArrayList<SalesLineItem> slis = new ArrayList<SalesLineItem>();
            slis = dbSli.getSalesOrders(salesOrder.getOrderId(), true);
            System.out.println("Antal " + slis.size());
            
            
            for(SalesLineItem sli : slis)
            {
                total = total + (sli.getProduct().getSalesPrice() * sli.getAmount());
            }
    
            if(total > 1500)
            {
                total = total - (total * 0.10);
            }
            salesOrder.setTotal(total);
            updateSalesOrderTotal(salesOrder);    
        }
    
    //Singelwhere is used when we only select one Sales Order 	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder salesObj = new SalesOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the sales order from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            salesObj = buildSalesOrder(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The customer is to be build as well
                               
                                IFDBCustomer dbCustomerObj = new DBCustomer();
                                Customer custObj = dbCustomerObj.findCustomer(salesObj.getCustomer().getCustomerId());
                                System.out.println("Customer is seleceted ");
                                salesObj.setCustomer(custObj);
                                
                                //The invoice is to be build as well
                                IFDBInvoice dbInvoiceObj = new DBInvoice();
                                Invoice invoice = dbInvoiceObj.findInvoice(salesObj.getInvoice().getInvoiceNo());
                                System.out.println("Invoice is selected");
                                salesObj.setInvoice(invoice);
                            }
                            else {
                                salesObj.setCustomer(null);
                            }
			}
                        else
                        {
                            salesObj = null;
                        }
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return salesObj;
	}
        
        private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the sales order from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		while( results.next() ){
	     	 SalesOrder saleObj = new SalesOrder();
		 saleObj = buildSalesOrder(results);	
                 list.add(saleObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   //The Customer is to be build as well
                     for(SalesOrder saleObj : list){
                        
                        IFDBCustomer dbCustObj = new DBCustomer();
                        Customer custObj = dbCustObj.findCustomer(saleObj.getCustomer().getCustomerId());
                        System.out.println("Customer is seleceted ");
                        saleObj.setCustomer(custObj);
                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
        
        //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM SalesOrder";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
        
        //method to build a sales order object
	private SalesOrder buildSalesOrder(ResultSet results)
      {   SalesOrder salesObj = new SalesOrder();
          try{
                salesObj.setOrderId(results.getInt("orderId"));
                salesObj.setDeliveryDate(results.getString("deliveryDate"));
                salesObj.setDeliveryStatus(results.getString("deliveryStatus"));
                salesObj.setDate(results.getString("date"));
	  	salesObj.setTotal(results.getDouble("total"));
	  	salesObj.setCustomer(new Customer(results.getInt("customerId")));
//              salesObj.setInvoice(new Invoice(results.getInt("invoiceNo")));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the sales order object");
         }
         return salesObj;
      }
        
    @Override
        public int getMaxId()
        {
            int maxOrderId = GetMax.getMaxId("Select max(orderId) from SalesOrder");
            return maxOrderId;
        }
}

